﻿Imports System.Data.SqlClient

Public Class cLawyer

    Dim con As New SqlConnection(strcon)

    Public Function select_Lawyer() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT LawyerID,LawyerName,Gender,DOB,[Address],Tel,Email FROM tblLawyer WHERE Active=1")
        Return dt
    End Function

    Public Function select_Lawyer_byID(ByVal LawyerID As Integer) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT LawyerID,LawyerName,Gender,DOB,[Address],Tel,Email,Photo FROM tblLawyer WHERE LawyerID=N'" & LawyerID & "'")
        Return dt
    End Function

    Public Function select_Lawyer_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT LawyerID,LawyerName,Gender,DOB,[Address],Tel,Email FROM tblLawyer WHERE Active=1 And LOWER(LawyerName + Gender + Address + Tel) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function insert_Lawyer(ByVal LawyerName As String, ByVal Gender As String, ByVal DOB As Date, ByVal Address As String, ByVal Tel As String, ByVal Email As String, ByVal Photo As Byte()) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblLawyer(LawyerName,Gender,DOB,[Address],Tel,Email,Photo) VALUES(@LawyerName,@Gender,@DOB,@Address,@Tel,@Email,@Photo)"
        With cmd.Parameters
            .AddWithValue("@LawyerName", LawyerName)
            .AddWithValue("@Gender", Gender)
            .AddWithValue("@DOB", DOB)
            .AddWithValue("@Address", Address)
            .AddWithValue("@Tel", Tel)
            .AddWithValue("@Email", Email)
            If Photo IsNot Nothing Then
                .AddWithValue("@Photo", Photo)
            Else
                .Add("@Photo", SqlDbType.Image).Value = DBNull.Value
            End If
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_Lawyer(ByVal LawyerID As Integer, ByVal LawyerName As String, ByVal Gender As String, ByVal DOB As Date, ByVal Address As String, ByVal Tel As String, ByVal Email As String, ByVal Photo As Byte()) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblLawyer SET LawyerName=@LawyerName,Gender=@Gender,DOB=@DOB,[Address]=@Address,Tel=@Tel,Email=@Email,Photo=@Photo WHERE LawyerID=@LawyerID"
        With cmd.Parameters
            .AddWithValue("@LawyerID", LawyerID)
            .AddWithValue("@LawyerName", LawyerName)
            .AddWithValue("@Gender", Gender)
            .AddWithValue("@DOB", DOB)
            .AddWithValue("@Address", Address)
            .AddWithValue("@Tel", Tel)
            .AddWithValue("@Email", Email)
            If Photo IsNot Nothing Then
                .AddWithValue("@Photo", Photo)
            Else
                .Add("@Photo", SqlDbType.Image).Value = DBNull.Value
            End If
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_Lawyer(ByVal LawyerID As Integer) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblLawyer SET Active=0 WHERE LawyerID=@LawyerID"
        With cmd.Parameters
            .AddWithValue("@LawyerID", LawyerID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function
End Class
